Data containers in large active relational databases often need to be reorganized and/or redefined. For example, a particular relational database table may be redefined to improve the performance of processing queries and data manipulation language (DML) operations against the particular relational table. The redefinition operation may involve, for example, adding a new column to the table. Tables may also have to be reorganized because the database and business applications that use the tables may require changes to the underlying structure of the tables. Tables that are to be reorganized and/or redefined are herein referred to as “target tables”.
In one approach, a target table is reorganized and/or redefined by locking the target table in a manner that prevents users from querying and performing DML operations on all or part of the target table. In other words, the target table is inaccessible to users for the entire duration of the reorganization and/or redefinition operation. The duration of inaccessibility to the data in the relational database is herein referred to as “downtime”. The problem with such an approach is that the downtime associated with the reorganization/redefinition may be unacceptably lengthy if the target tables are massive and the reorganization/redefinition is extensive. In addition, it is difficult to implement such a feature when dependent objects, such as indexes, are also involved.
In the conventional approach, the target table is taken “offline” for the purpose of reorganization/redefinition. Thus, the target table is inaccessible to users during the period that the target is offline. For example, a given target table is first exported from the relational database for the process of reorganization/redefinition. After the target table is reorganized/redefined, the target table is imported back into the relational database. One problem with such an approach is that indexes, constraints, and triggers that are associated with a target table are typically dropped in order to speed up the import process. Thus, the indexes, constraints, and triggers associated with the target table need to be re-created. Each hour of downtime can cost millions of dollars for a corporation.
Based on the foregoing, it is clearly desirable to provide a mechanism for reorganizing/redefining data in relational databases with little or no downtime.